﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using Microsoft.Web.UI.WebControls;
using VitCode.Castle.Helper;
using VitCode.Web.UI;
using VitCode.Web.UI.ExcelFileGenerator;

namespace WebDataProcessing
{
    public partial class Index : System.Web.UI.Page
    {
        private readonly ControlHelper ctlHlp = new ControlHelper();

        private SqlHelper SqlHlp
        {
            get
            {
                if (Session["sqlHlp"] == null)
                    Session["sqlHlp"] = new SqlHelper(txtSqlConnStr.Text);
                return ((SqlHelper)Session["sqlHlp"]);
            }
            set { Session["sqlHlp"] = value; }
        }

        public DataFixImportor Importor
        {
            get { return (DataFixImportor)Session["Importor"]; }
            set { Session["Importor"] = value; }
        }

        private ErrorResult ErrorMessageResult
        {
            get
            {
                if (Session["ErrorMessageResult"] == null)
                {
                    return new ErrorResult();
                }
                return (ErrorResult)Session["ErrorMessageResult"];
            }
            set { Session["ErrorMessageResult"] = value; }
        }

        private DataTable ErrorDataResult
        {
            get
            {
                if (Session["ErrorDataResult"] == null)
                {
                    return new DataTable();
                }
                return (DataTable)Session["ErrorDataResult"];
            }
            set { Session["ErrorDataResult"] = value; }
        }

        private void Page_Load(object sender, EventArgs e)
        {
            pgList.DataSourceDelegate = new PaginationGrid.GridDataSourceDelegate(BindQueryList);
            pgError.DataSourceDelegate = new PaginationGrid.GridDataSourceDelegate(BindErrorMessages);
            pgErrorData.DataSourceDelegate = new PaginationGrid.GridDataSourceDelegate(BindErrorData);
            btnImport.Attributes.Add("onclick",
                                     "setTimeout('document.all(\"" + btnImport.ClientID + "\").disabled=true;',10);");
            btnCheckData.Attributes.Add("onclick",
                                        "setTimeout('document.all(\"" + btnCheckData.ClientID +
                                        "\").disabled=true;',10);");
            btnOutput.Enabled = (Importor != null);
            if (!IsPostBack)
            {
                try
                {
                    Importor = null;
                    ErrorMessageResult = null;
                    SetTableCard(tblDB.ID);
                    //SetPageKeyControls();
                    SetDBConfig();
                }
                catch (Exception ex)
                {
                    Alert.MessageBox(ex.Message);
                }

            }
        }

        private void SetTableCard(string tableId)
        {
            tblDB.Visible = false;
            tblImport.Visible = false;
            tblQuery.Visible = false;
            tlbErrorData.Visible = false;
            Page.FindControl(tableId).Visible = true;
        }

        private string CreateUserId()
        {
            return Request.UserHostName + "___" + Request.UserHostAddress;
        }

        private void SaveDBConfig()
        {
            var usHelper = new UserSettingHelper();
            string strUserId = CreateUserId();
            string strTableName = ddlTableName.SelectedValue;
            string strKeyColName = ddlKeyColName.SelectedValue;
            string strCnStr = txtSqlConnStr.Text;
            usHelper.SetDBConfig(strUserId, strCnStr, strTableName, strKeyColName);
        }

        private void SetDDLValue(string strValue, DropDownList ddl)
        {
            if (strValue.Length > 0 && ddl.Items.FindByValue(strValue) != null)
                ddl.SelectedValue = strValue;
        }

        private void SetDBConfig()
        {
            var usHelper = new UserSettingHelper();
            string strUserId = CreateUserId();
            DataTable dt = usHelper.GetDBConfig(strUserId);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                string strCnStr = row[usHelper.CN_STR].ToString();
                string strTableName = row[usHelper.TABLE_NAME].ToString();
                string strKeyColName = row[usHelper.KEY_COL_NAME].ToString();
                if (strCnStr.Length > 0)
                    txtSqlConnStr.Text = strCnStr;
                SetDDLValue(strTableName, ddlTableName);
                SetQueryColumnsInfo(ddlTableName.SelectedValue);
                SetDDLValue(strKeyColName, ddlKeyColName);
            }
        }

        private void SetPageKeyControls()
        {
            SqlHlp.ConnStr = txtSqlConnStr.Text;
            DataTable dt = SqlHlp.GetTableInfo(txtTableName.Text);
            ctlHlp.SetDropDownListItems(ddlTableName, dt, 0, 0);
            dt.Dispose();
            SetQueryColumnsInfo(ddlTableName.SelectedValue);
        }

        private void SetQueryColumnsInfo(string tableName)
        {
            string sql =
                "select c.Name from sysobjects o inner join syscolumns c on o.Id=c.Id and o.Name=N'" + tableName +
                "' order by c.colorder";
            DataTable dtColInfo = SqlHlp.GetDataTable(sql);
            ctlHlp.SetDropDownListItems(ddlQueryColumn, dtColInfo, 0, 0);
            ctlHlp.SetDropDownListItems(ddlKeyColName, dtColInfo, 0, 0);
            dtColInfo.Dispose();
        }

        protected void btnQuery_Click(object sender, EventArgs e)
        {
            pgList.BindGrid();
            SaveDBConfig();
        }

        private object BindQueryList()
        {
            string strTopInfo = "";
            if (txtTop.Text.Length > 0)
                strTopInfo = "top " + txtTop.Text + " ";
            return
                SqlHlp.GetDataTableByGenrenalQuery(ddlTableName.SelectedValue, ddlQueryColumn.SelectedValue,
                                                   txtQueryInfo.Text,
                                                   ddlQueryType.SelectedValue, strTopInfo);
        }

        private object BindErrorMessages()
        {
            return ErrorMessageResult;
        }

        private object BindErrorData()
        {
            if (cbContainErrMsg.Checked && ErrorDataResult != null && ErrorMessageResult != null
                && ErrorDataResult.Rows.Count > 0 && ErrorMessageResult.Rows.Count > 0
                && ErrorDataResult.Rows.Count == ErrorMessageResult.Rows.Count)
            {
                DataTable dt = null;

                for (int i = 0; i < ErrorDataResult.Rows.Count; i++)
                {
                    if (i == 0)
                    {
                        dt = ErrorDataResult.Clone();
                        for (int k = 0; k < ErrorMessageResult.Columns.Count; k++)
                            dt.Columns.Add(ErrorMessageResult.Columns[k].ColumnName);
                    }
                    int sourcedColAmount = ErrorDataResult.Columns.Count;
                    var objs = new object[dt.Columns.Count];
                    ErrorDataResult.Rows[i].ItemArray.CopyTo(objs, 0);
                    ErrorMessageResult.Rows[i].ItemArray.CopyTo(objs, sourcedColAmount);
                    DataRow r = dt.NewRow();
                    r.ItemArray = objs;
                    dt.Rows.Add(r);
                }
                return dt;
            }

            return ErrorDataResult;
        }

        protected void btnCheckData_Click(object sender, EventArgs e)
        {
            Importor = null;
            btnImport.Enabled = false;
            DateTime S_datetime = DateTime.Now;
            lblMessage.Text = "";
            if (fileSource.Value == "")
            {
                lblCheckSource.Text = "请选择导入文件";
                return;
            }

            string strFileName = fileSource.PostedFile.FileName;
            int index = strFileName.LastIndexOf("\\");
            strFileName = strFileName.Substring(index + 1, strFileName.Length - index - 1);
            lblFileName.Text = strFileName;


            if (strFileName.Substring(strFileName.Length - 4).ToLower() != ".xls")
            {
                lblMessage.Text = "请使用正确的模板文件";
                pgError.Visible = false;
                pgErrorData.Visible = false;
                return;
            }

            string path = Path.GetTempPath() + DateTime.Now.Ticks.ToString().Substring(5) + strFileName;
            fileSource.PostedFile.SaveAs(path);

            try
            {
                var scDataCols = new StringCollection();
                for (int i = 0; i < ddlQueryColumn.Items.Count; i++)
                    scDataCols.Add(ddlQueryColumn.Items[i].Value);
                Importor = new DataFixImportor(path, txtSheetName.Text, ddlTableName.SelectedValue,
                                               ddlKeyColName.SelectedValue, scDataCols);
                Importor.ConnStr = SqlHlp.ConnStr;
                Importor.IsPurpose = true;
                Importor.Check();
                DataTable dt = Importor.GetSourceData(true);
                int sourceDataCount = dt.Rows.Count;
                int errorDataCount = 0;
                if (Importor.ErrorMessage.Rows.Count == 0)
                {
                    pgError.Visible = false;
                    ErrorDataResult = null;
                    ErrorMessageResult = null;
                }
                else
                {
                    pgError.Visible = true;
                    pgError.SortExpression = "行号";
                    ErrorMessageResult = Importor.ErrorMessage;
                    pgError.BindGrid();
                    ErrorDataResult = Importor.GetSourceData(false);
                    errorDataCount = ErrorDataResult.Rows.Count;
                }

                DateTime F_Datetime = DateTime.Now;
                TimeSpan showtime = F_Datetime - S_datetime;
                lblMessage.Text = "正确数据： " + sourceDataCount + " 条，错误数据： " + errorDataCount + " 条，校验所耗时间：" +
                                  showtime.Hours + " 时 " +
                                  showtime.Minutes + " 分 " + showtime.Seconds + " 秒";
                btnImport.Enabled = sourceDataCount > 0;
            }
            catch (ArgumentNullException ex)
            {
                Alert.MessageBox(ex.Message);
            }
            catch (ArgumentOutOfRangeException ex2)
            {
                Alert.MessageBox(ex2.Message);
            }
            catch (OleDbException ex4)
            {
                if (ex4.ErrorCode == -2147467259)
                    Alert.MessageBox("在上传附件中找不到表名为 “" + txtSheetName.Text + "” 的工作表");
            }
            btnOutput.Enabled = (Importor != null);
            SaveDBConfig();
        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
            DateTime S_datetime = DateTime.Now;
            lblMessage.Text = "";
            try
            {
                Importor.HistoryMemo = txtHistoryMemo.Text;
                Importor.Import(Importor.GetSourceData(true), false);

                pgError.Visible = false;
                ErrorDataResult = null;
                ErrorMessageResult = null;

                DateTime F_Datetime = DateTime.Now;
                TimeSpan showtime = F_Datetime - S_datetime;
                lblMessage.Text = "导入数据： " + Importor.InsertCount + " 条，更新数据： " + Importor.UpdateCount
                                  + " 条，导入所耗时间：" + showtime.Hours + " 时 " + showtime.Minutes + " 分 " + showtime.Seconds +
                                  " 秒";
            }
            catch (ArgumentNullException ex)
            {
                Alert.MessageBox(ex.Message);
            }
            catch (ArgumentOutOfRangeException ex2)
            {
                Alert.MessageBox(ex2.Message);
            }
            catch (OleDbException ex4)
            {
                if (ex4.ErrorCode == -2147467259)
                    Alert.MessageBox("在上传附件中找不到表名为 “" + txtSheetName.Text + "” 的工作表");
            }
            finally
            {
                Importor = null;
                btnImport.Enabled = false;
            }
        }

        protected void btnOutput_Click(object sender, EventArgs e)
        {
            int errorMsgColIndex = 1, errorRowNumColIndex = 0;
            if (Importor == null)
                return;
            DataTable result = null;
            DataTable source = Importor.SourceData;
            var tmpHt = new Hashtable();
            if (source == null || source.Rows.Count == 0)
            {
                Alert.MessageBox("模板中没有数据");
                return;
            }
            for (int i = 0; i < ErrorMessageResult.Rows.Count; i++)
            {
                DataRow r = ErrorMessageResult.Rows[i];
                tmpHt.Add(r[errorRowNumColIndex], r[errorMsgColIndex]);
            }
            for (int i = 0; i < source.Rows.Count; i++)
            {
                if (i == 0)
                {
                    result = ErrorDataResult.Clone();
                    result.Columns.Add("检验结果");
                    result.Columns.Add("出错信息");
                }
                var objs = new object[result.Columns.Count];
                source.Rows[i].ItemArray.CopyTo(objs, 0);
                string[] tempObjs;
                if (tmpHt.ContainsKey(i + 2))
                    tempObjs = new[] { "错误", tmpHt[i + 2].ToString() };
                else
                    tempObjs = new[] { "正确" };
                tempObjs.CopyTo(objs, source.Columns.Count);
                DataRow r = result.NewRow();
                r.ItemArray = objs;
                result.Rows.Add(r);
            }
            var exporter = new ExcelFileGenerator();
            string tempPath = exporter.ExportDataToExcelFile(result);
            Response.Charset = "GB2312";
            Response.AppendHeader("Content-Disposition",
                                  "attachment;filename=" +
                                  HttpUtility.UrlEncode("导入模板输出(带校验信息)_" + DateTime.Now.ToString("yyyyMMdd") + ".xls"));
            Response.ContentEncoding = Encoding.Default;
            Response.ContentType = "application/ms-excel";
            Response.Clear();
            Response.WriteFile(tempPath);
            Response.End();
        }

        protected void pgMenu_MenuItemClick(object sender, MenuEventArgs e)
        {
            if (pgMenu.SelectedValue == "0")
            {
                SetTableCard(tblDB.ID);
            }
            else if (pgMenu.SelectedValue == "1")
            {
                if (ddlKeyColName.SelectedIndex > -1)
                    SetTableCard(tblQuery.ID);
                else
                    Alert.MessageBox("请先选择操作表的关键字段");
            }
            else if (pgMenu.SelectedValue == "2")
            {
                if (ddlKeyColName.SelectedIndex > -1)
                {
                    SetTableCard(tblImport.ID);
                    pgError.BindGrid();
                }
                else
                    Alert.MessageBox("请先选择操作表的关键字段");
            }
            else if (pgMenu.SelectedValue == "3")
            {
                if (ddlKeyColName.SelectedIndex > -1)
                {
                    SetTableCard(tlbErrorData.ID);
                    pgErrorData.BindGrid();
                }
                else
                    Alert.MessageBox("请先选择操作表的关键字段");
                
            }
        }

        protected void btnSeachTable_Click(object sender, EventArgs e)
        {
            SetPageKeyControls();
        }

        protected void ddlTableName_SelectedIndexChanged1(object sender, EventArgs e)
        {
            SetQueryColumnsInfo(ddlTableName.SelectedValue);
        }
    }
}